package com.yourenbang.lingyun.transaction.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.util.ObjectUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;

public class ExportExcelUtils {


    public static <T> void exportExcel(String title, String[] headers, String[] fieldNames, List<T> dataset, HttpServletRequest request, HttpServletResponse response) {
        SXSSFWorkbook workbook = null;
        try {
            // 产生工作薄对象
            workbook = new SXSSFWorkbook(1000);
            //产生工作表对象
            SXSSFSheet sheet = workbook.createSheet();
            //创建标题
            createExcelHeaders(workbook, sheet, headers);
            //创建内容
            createExcelContent(workbook, sheet, fieldNames, dataset, 2);
            String rtn = getFileNameByBrowser(title, request);
            setDownloadBrowserHead(rtn, response);
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel文件
     *
     * @param title      表格标题名
     * @param headers    表格第一行名称
     * @param fieldNames 实体字段
     * @param dataset    数据集合
     * @param request
     * @param response
     */
    public static <T> void exportExcel(int sheetCount, String[] sheetName,
                                       String title, String[] warning,
                                       List<String[]> headers, List<String[]> fieldNames,
                                       List<List<T>> dataset, HttpServletRequest request, HttpServletResponse response) {
        SXSSFWorkbook workbook = null;
        try {
            // 产生工作薄对象
            workbook = new SXSSFWorkbook(1000);
            for (int i = 0; i < sheetCount; i++) {
                //产生工作表对象
                SXSSFSheet sheet = workbook.createSheet();
                workbook.setSheetName(i, sheetName[i]);
                //创建标题
                createExcelHeaders(workbook, sheet, headers.get(i), warning[i]);
                //创建内容
                createExcelContent(workbook, sheet, fieldNames.get(i), dataset.get(i), 2);
            }
            String rtn = getFileNameByBrowser(title, request);
            setDownloadBrowserHead(rtn, response);
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void setDownloadBrowserHead(String name, HttpServletResponse response) {
        response.setContentType("application/octet-stream");
        response.addHeader("Content-disposition", "attachment;" + name + ".xlsx");
    }

    private static <T> void createExcelContent(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] fieldNames, List<T> dataset, int begin) throws NoSuchFieldException, IllegalAccessException {
        //内容字体
        Font columnHeadFont = workbook.createFont();
        columnHeadFont.setFontName("微软雅黑");
        columnHeadFont.setFontHeightInPoints((short) 10);
        //内容的样式
        CellStyle columnHeadStyle = workbook.createCellStyle();
        columnHeadStyle.setFont(columnHeadFont);
        columnHeadStyle.setAlignment(HorizontalAlignment.LEFT);
        columnHeadStyle.setVerticalAlignment(VerticalAlignment.TOP); //上下居中
        columnHeadStyle.setLocked(true);
        columnHeadStyle.setWrapText(false); //自动换行
        columnHeadStyle.setBorderBottom(BorderStyle.THIN);
        columnHeadStyle.setBorderLeft(BorderStyle.THIN);
        columnHeadStyle.setBorderRight(BorderStyle.THIN);
        columnHeadStyle.setBorderTop(BorderStyle.THIN);
        //写入各条记录，每条记录对应excel表中的一行
        for (int i = 0; i < dataset.size(); i++) {
            T t = dataset.get(i);
            SXSSFRow row = sheet.createRow(i + begin);
            for (int j = 0; j < fieldNames.length; j++) {

                SXSSFCell cell = row.createCell((short) j);
                if (t instanceof HashMap) {
                    Object value = ((HashMap) t).get(fieldNames[j]);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(new XSSFRichTextString(ObjectUtils.isEmpty(value) ? "" : value + ""));
                    cell.setCellStyle(columnHeadStyle);
                } else {
                    Field field = t.getClass().getDeclaredField(fieldNames[j]);
                    field.setAccessible(true);
                    String type = field.getType().getName();
                    if (type.contains("int") || type.contains("long") ||
                            type.contains("double") || type.contains("BigDecimal")) {
                        cell.setCellType(CellType.NUMERIC);
                    } else {
                        cell.setCellType(CellType.STRING);
                    }
                    Object value = field.get(t);
                    if (value == "" || value == "null" || value == null) {
                        value = "";
                    }
                    cell.setCellValue(new XSSFRichTextString(value == "" ? "" : String.valueOf(value.toString())));
                    field.setAccessible(false);
                    cell.setCellStyle(columnHeadStyle);
                }
            }
        }
    }

    private static void createExcelHeaders(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] headers) {
        createExcelHeaders(workbook, sheet, headers, null);
    }

    private static void createExcelHeaders(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] headers, String warning) {
        //设置字体
        Font headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short) 12);

        //另一种样式
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setLocked(true);
        headStyle.setWrapText(false); //自动换行
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        //产生注意行
        if (org.apache.commons.lang3.StringUtils.isNotBlank(warning)) {
            Font headFont0 = workbook.createFont();
            headFont0.setFontName("微软雅黑");
            headFont0.setFontHeightInPoints((short) 10);
            headFont0.setColor(Font.COLOR_RED);
            CellStyle headStyle0 = workbook.createCellStyle();
            headStyle0.setFont(headFont0);

            SXSSFRow row0 = sheet.createRow(0);
            row0.setHeight((short) 1000);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
            SXSSFCell cell0 = row0.createCell(0);
            cell0.setCellValue(warning);
            cell0.setCellStyle(headStyle0);
        }

        //产生第一行
        SXSSFRow row = sheet.createRow(1);
        //产生单元格
        SXSSFCell cell;
        //写入各个字段的名称
        for (int i = 0; i < headers.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
            //创建第一行各个字段名称的单元格
            cell = row.createCell((short) i);
            //设置单元格内容为字符串类型
            cell.setCellType(CellType.STRING);
            //赋值
            cell.setCellValue(new XSSFRichTextString(headers[i]));
            cell.setCellStyle(headStyle);
        }
    }

    private static void createMerchantBillsDetailsExcelHeaders(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] headers, String warning, String warning1) {
        //设置字体
        Font headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short) 12);

        //另一种样式
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setLocked(true);
        headStyle.setWrapText(false); //自动换行
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);

        //产生注意行
        Font headFont0 = workbook.createFont();
        headFont0.setFontName("微软雅黑");
        headFont0.setFontHeightInPoints((short) 10);
        headFont0.setColor(Font.COLOR_RED);
        CellStyle headStyle0 = workbook.createCellStyle();
        headStyle0.setFont(headFont0);

        SXSSFRow row0 = sheet.createRow(0);
//		row0.setHeight((short)1000);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        SXSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue(warning);
        cell0.setCellStyle(headStyle0);

        SXSSFRow row1 = sheet.createRow(1);
//		row1.setHeight((short)1000);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
        cell0 = row1.createCell(0);
        cell0.setCellValue(warning1);
        cell0.setCellStyle(headStyle0);


        //产生第一行
        SXSSFRow row = sheet.createRow(3);
        //产生单元格
        SXSSFCell cell;
        //写入各个字段的名称
        for (int i = 0; i < headers.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
            //创建第一行各个字段名称的单元格
            cell = row.createCell((short) i);
            //设置单元格内容为字符串类型
            cell.setCellType(CellType.STRING);
            //赋值
            cell.setCellValue(new XSSFRichTextString(headers[i]));
            cell.setCellStyle(headStyle);
        }
    }

    /**
     * 处理不同浏览器名称乱码的问题
     *
     * @param fileName 文件名
     * @param request  客户端请求
     * @return 文件名
     * @throws UnsupportedEncodingException
     */
    private static String getFileNameByBrowser(String fileName, HttpServletRequest request) throws UnsupportedEncodingException {
        String userAgent = request.getHeader("User-Agent");
        String newFileName = URLEncoder.encode(fileName, "UTF-8");
        String rtn = "filename=" + newFileName + "";
        //处理不同浏览器下载时文件名乱码问题
        if (userAgent != null) {
            userAgent = userAgent.toLowerCase();
            //IE浏览器
            if (userAgent.indexOf("msie") != -1) {
                rtn = "filename=" + newFileName + "";
            }
            //Opera
            else if (userAgent.indexOf("opera") != -1) {
                rtn = "filename*=UTF-8''" + newFileName;
            }
            //Safari
            else if (userAgent.indexOf("safari") != -1) {
                rtn = "filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "";
            }
            //Chrome
            else if (userAgent.indexOf("applewebkit") != -1) {
                rtn = "filename=" + newFileName + "";
            }
            //FireFox
            else if (userAgent.indexOf("mozilla") != -1) {
                rtn = "filename*=UTF-8''" + newFileName;
            }
        }
        return rtn;
    }
}
